package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import util.db.DBConn;
import dao.LearnResMagDao;
import domain.LearnRes;

/**
 * 
 * Jun 2, 2009
 *
 * @author Fabian
 *
 */
public class LearnResMagImpl implements LearnResMagDao {

	/* (non-Javadoc)
	 * @see dao.LearnResMagDao#getPageTestLearnRes(int, int)
	 */
	
	public List<LearnRes> getPageTestLearnRes(int start, int limit) {
		List<LearnRes> list = new ArrayList<LearnRes>();		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select * from t_learn_res where sort = 'experiment' order by pub_time desc limit ?, ?";
		try {
			conn = DBConn.getConn();
			pstmt = DBConn.getPstmt(conn, sql);
			pstmt.setInt(1, start);
			pstmt.setInt(2, limit);
			rs = pstmt.executeQuery();			
			while (rs.next()) {
				LearnRes lr = new LearnRes();
				lr.setId(rs.getInt("id"));
				lr.setTitle(rs.getString("title"));
				lr.setRes_desc(rs.getString("res_desc"));
				lr.setPath(rs.getString("path"));
				lr.setFormat(rs.getString("format"));
				lr.setSize(rs.getString("size"));
				lr.setPub_user(rs.getString("pub_user"));
				lr.setSort(rs.getString("sort"));
				lr.setPub_time(rs.getTimestamp("pub_time"));
				lr.setSee_count(rs.getInt("see_count"));
				lr.setDown_count(rs.getInt("down_count"));
				
				list.add(lr);
			}
			return list;
		} catch(SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
	}

	/* (non-Javadoc)
	 * @see dao.LearnResMagDao#getTestTotalCount()
	 */
	
	public int getTestTotalCount() {
		int totalCount = 0;
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select count(*) from t_learn_res where sort = 'experiment'";
		try {
			conn = DBConn.getConn();
			pstmt = DBConn.getPstmt(conn, sql);
			rs = pstmt.executeQuery();			
			if (rs.next()) {
				totalCount = rs.getInt(1);
				return totalCount;
			}
		} catch(SQLException e) {
			e.printStackTrace();
			return totalCount;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
		return 0;
	}

	/* (non-Javadoc)
	 * @see dao.LearnResMagDao#getLearnRes(int)
	 */
	
	public LearnRes getLearnRes(int id) {
		LearnRes lr = null;		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select * from t_learn_res where id = ?";
		try {
			conn = DBConn.getConn();
			pstmt = DBConn.getPstmt(conn, sql);
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();			
			if (rs.next()) {
				lr = new LearnRes();
				lr.setId(rs.getInt("id"));
				lr.setTitle(rs.getString("title"));
				lr.setRes_desc(rs.getString("res_desc"));
				lr.setPath(rs.getString("path"));
				lr.setFormat(rs.getString("format"));
				lr.setSize(rs.getString("size"));
				lr.setPub_user(rs.getString("pub_user"));
				lr.setSort(rs.getString("sort"));
				lr.setPub_time(rs.getTimestamp("pub_time"));
				lr.setSee_count(rs.getInt("see_count"));
				lr.setDown_count(rs.getInt("down_count"));
			}
			return lr;
		} catch(SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
	}

	/* (non-Javadoc)
	 * @see dao.LearnResMagDao#addLearnRes(vo.LearnRes)
	 */
	
	public boolean addLearnRes(LearnRes n) {
		boolean result = false;
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "insert into t_learn_res values (null, ?, ?, ?, ?, ?, ?, ?, now(), 0, 0)";
		try {
			conn = DBConn.getConn();
			pstmt = DBConn.getPstmt(conn, sql);
			pstmt.setString(1, n.getTitle());
			pstmt.setString(2, n.getRes_desc());
			pstmt.setString(3, n.getPath());
			pstmt.setString(4, n.getFormat());
			pstmt.setString(5, n.getSize());
			pstmt.setString(6, n.getPub_user());
			pstmt.setString(7, n.getSort());
			int rsColum = pstmt.executeUpdate();		
			if (rsColum > 0) {
				result = true;
				return result;
			}
		} catch(SQLException e) {
			e.printStackTrace();
			return result;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
		
		return result;
	}

	/* (non-Javadoc)
	 * @see dao.LearnResMagDao#delLearnRes(int)
	 */
	
	public boolean delLearnRes(int id) {
		boolean result = false;
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "delete from t_learn_res where id = ?";
		try {
			conn = DBConn.getConn();
			pstmt = DBConn.getPstmt(conn, sql);
			pstmt.setInt(1, id);
			int rsColum = pstmt.executeUpdate();		
			if (rsColum > 0) {
				result = true;
				return result;
			}
		} catch(SQLException e) {
			e.printStackTrace();
			return result;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
		
		return result;
	}

	/* (non-Javadoc)
	 * @see dao.LearnResMagDao#getPageNotice(int, int)
	 */
	
	public List<LearnRes> getPageLearnRes(int start, int limit) {
		List<LearnRes> list = new ArrayList<LearnRes>();		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select * from t_learn_res where sort = 'courseware' order by pub_time desc limit ?, ?";
		try {
			conn = DBConn.getConn();
			pstmt = DBConn.getPstmt(conn, sql);
			pstmt.setInt(1, start);
			pstmt.setInt(2, limit);
			rs = pstmt.executeQuery();			
			while (rs.next()) {
				LearnRes lr = new LearnRes();
				lr.setId(rs.getInt("id"));
				lr.setTitle(rs.getString("title"));
				lr.setRes_desc(rs.getString("res_desc"));
				lr.setPath(rs.getString("path"));
				lr.setFormat(rs.getString("format"));
				lr.setSize(rs.getString("size"));
				lr.setPub_user(rs.getString("pub_user"));
				lr.setSort(rs.getString("sort"));
				lr.setPub_time(rs.getTimestamp("pub_time"));
				lr.setSee_count(rs.getInt("see_count"));
				lr.setDown_count(rs.getInt("down_count"));
				
				list.add(lr);
			}
			return list;
		} catch(SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
	}

	/* (non-Javadoc)
	 * @see dao.LearnResMagDao#getTotalCount()
	 */
	
	public int getTotalCount() {
		int totalCount = 0;
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select count(*) from t_learn_res where sort = 'courseware'";
		try {
			conn = DBConn.getConn();
			pstmt = DBConn.getPstmt(conn, sql);
			rs = pstmt.executeQuery();			
			if (rs.next()) {
				totalCount = rs.getInt(1);
				return totalCount;
			}
		} catch(SQLException e) {
			e.printStackTrace();
			return totalCount;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
		return 0;
	}

	/* (non-Javadoc)
	 * @see dao.LearnResMagDao#modifyLearnRes(vo.LearnRes)
	 */
	
	public boolean modifyLearnRes(LearnRes n) {
		boolean result = false;
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "update t_learn_res set title=?, res_desc=? where id = ?";
		try {
			conn = DBConn.getConn();
			pstmt = DBConn.getPstmt(conn, sql);
			pstmt.setString(1, n.getTitle());
			pstmt.setString(2, n.getRes_desc());
			pstmt.setInt(3, n.getId());
			int rsColum = pstmt.executeUpdate();		
			if (rsColum > 0) {
				result = true;
				return result;
			}
		} catch(SQLException e) {
			e.printStackTrace();
			return result;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
		
		return result;
	}

	/* (non-Javadoc)
	 * @see dao.LearnResMagDao#addDownCount(int)
	 */
	
	public boolean addDownCount(int id) {
		boolean result = false;
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "update t_learn_res set down_count = down_count + 1 where id = ?";
		try {
			conn = DBConn.getConn();
			pstmt = DBConn.getPstmt(conn, sql);
			pstmt.setInt(1, id);
			int rsColum = pstmt.executeUpdate();		
			if (rsColum > 0) {
				result = true;
				return result;
			}
		} catch(SQLException e) {
			e.printStackTrace();
			return result;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
		
		return result;
	}

	/* (non-Javadoc)
	 * @see dao.LearnResMagDao#addSeeCount(int)
	 */
	
	public boolean addSeeCount(int id) {
		boolean result = false;
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "update t_learn_res set see_count = see_count + 1 where id = ?";
		try {
			conn = DBConn.getConn();
			pstmt = DBConn.getPstmt(conn, sql);
			pstmt.setInt(1, id);
			int rsColum = pstmt.executeUpdate();		
			if (rsColum > 0) {
				result = true;
				return result;
			}
		} catch(SQLException e) {
			e.printStackTrace();
			return result;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
		
		return result;
	}

}
